USE SlidingWindow;
GO

/*
A few simple INSERT statements act as our ETL process, loading into our load staging table.
These statements are grouped into days, mimicking the recurring load.
A couple of SELECT statements give us some insight into the number of partitions,
and the amount of data contained in each one allowing us to track the processing of our loads into the fact table.

For each day, execute the INSERT statement. Note that the records are inserted into the staging table.
Next, execute the sliding window stored procedure.
The procedure should execute without issue and apply the sliding window load into the fact table.
In between each day’s load, query the fact table to see the new data added.
Also, run the SELECT statements to see the partition break down and where the inserted rows are now living in each partition.
*/
-- Day 1:
INSERT INTO Staging.FactTable_Loading
	VALUES(1,1,20120701,100,500);
 
EXECUTE dbo.SlideRangeLeftWindow @RetentionDays = 1, @RunDate = '2012-07-01';
EXECUTE dbo.GetPartitionInfo;
GO

-- Day 2:
INSERT INTO Staging.FactTable_Loading
	VALUES (3,3,20120702,200,600), (2,2,20120702,150,550),
		(4,4,20120702,250,650), (5,5,20120702,300,700);
 
EXECUTE dbo.SlideRangeLeftWindow @RetentionDays = 1, @RunDate = '2012-07-02';
EXECUTE dbo.GetPartitionInfo;
GO

-- Day 3:
INSERT INTO Staging.FactTable_Loading
	VALUES(6,6,20120703,350,750), (7,7,20120703,400,800),
		(8,8,20120703,450,850), (9,9,20120703,500,900);

EXECUTE dbo.SlideRangeLeftWindow @RetentionDays = 1, @RunDate = '2012-07-03';
EXECUTE dbo.GetPartitionInfo;
GO

-- Day 4:
INSERT INTO Staging.FactTable_Loading
	VALUES(10,10,20120704,550,950), (11,11,20120704,600,1000), (12,12,20120704,700,1050),
		(13,13,20120704,750,1100), (14,14,20120704,800,1150);

EXECUTE dbo.SlideRangeLeftWindow @RetentionDays = 1, @RunDate = '2012-07-04';
EXECUTE dbo.GetPartitionInfo;
GO

-- Day 5:
INSERT INTO Staging.FactTable_Loading
	VALUES(15,15,20120705,850,1200);

EXECUTE dbo.SlideRangeLeftWindow @RetentionDays = 1, @RunDate = '2012-07-05';
EXECUTE dbo.GetPartitionInfo;
GO
 
-- Day 6:
INSERT INTO Staging.FactTable_Loading
	VALUES(15,15,20120706,850,1200);
 
EXECUTE dbo.SlideRangeLeftWindow @RetentionDays = 1, @RunDate = '2012-07-06';
EXECUTE dbo.GetPartitionInfo;
GO